package com.example.springbootbabyu.mapper;

import com.example.springbootbabyu.entity.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface GoodsMapper {
    @Select("SELECT now_price, old_price, stock, discount, is_effective FROM sku WHERE id = #{skuId}")
    Sku selectStockBySkuId(@Param("skuId") String skuId);
    // 查询商品基本信息
    @Select("SELECT id, name, spu_code, `desc`, price, old_price, " +
            "discount, inventory, brand_id, sales_count, " +
            "comment_count, collect_count, video_scale, `main_Picture`, category_id FROM goods WHERE id = #{goodsId}")
    GoodsDetailDTO selectGoodsBaseInfo(@Param("goodsId") String goodsId);

    // 查询品牌信息
    @Select("SELECT id, name, name_en as nameEn, logo, picture FROM brand WHERE id = #{brandId}")
    Brand selectBrandById(@Param("brandId") String brandId);

    // 查询主图视频
    @Select("SELECT url FROM goods_video WHERE goods_id = #{goodsId} ORDER BY sort")
    List<String> selectMainVideos(@Param("goodsId") String goodsId);

    // 查询主图
    @Select("SELECT pictures FROM goods_picture WHERE goods_id = #{goodsId}")
    List<String> selectMainPictures(@Param("goodsId") String goodsId);

    // 查询规格
    @Select("SELECT id, name FROM spec WHERE goods_id = #{goodsId}")
    List<GoodsSpec> selectSpecs(@Param("goodsId") String goodsId);

    // 查询规格值
    @Select("SELECT name, picture, `desc`, selected, disabled FROM spec_value WHERE spec_id = #{specId}")
    List<SpecValue> selectSpecValues(@Param("specId") String specId);

    // 查询SKU
    @Select("SELECT id,specs as skuCode,price,old_price,stock as inventory,specs FROM sku WHERE goods_id = #{goodsId}")
    List<GoodsSku> selectSkus(@Param("goodsId") String goodsId);

    // 查询SKU规格
    @Select("SELECT s.name, sv.name as valueName FROM spec s join spec_value sv ON s.id = sv.spec_id WHERE s.goods_id = #{goodsId}")
    List<SkuSpec> selectSkuSpecs(@Param("goodsId")String goodsId);

    // 查询分类
    @Select("SELECT * FROM category WHERE id = #{categoryId}")
    List<CategoryDTO> selectCategories(String categoryId);

    // 查询父分类
    @Select("SELECT * FROM category WHERE id = #{parentId}")
    CategoryDTO selectParentCategory(@Param("parentId") String parentId);

    // 查询详情图片
    @Select("SELECT pictures FROM goods_picture WHERE goods_id = #{goodsId}")
    List<String> selectDetailPictures(@Param("goodsId") String goodsId);

    // 查询商品属性
    @Select("SELECT name, value FROM goods_property WHERE goods_id = #{goodsId}")
    List<GoodsProperty> selectProperties(@Param("goodsId") String goodsId);

    // 查询相似商品
    @Select("SELECT id, name, `desc`, price, picture, order_num as orderNum " +
            "FROM goods WHERE category_id IN " +
            "(SELECT category_id FROM goods_category WHERE goods_id = #{goodsId}) " +
            "AND id != #{goodsId} ORDER BY sales_count DESC LIMIT 6")
    List<SimilarProduct> selectSimilarProducts(@Param("goodsId") String goodsId);

    // 查询每日热销
    @Select("SELECT id, name, `desc`, price, picture, order_num as orderNum " +
            "FROM goods ORDER BY sales_count DESC LIMIT 8")
    List<HotProduct> selectHotByDay();

    @Select("SELECT * FROM goods where id = #{id}")
    List<HotGoodsVO> selectHotGoods(String id, String type, Integer limit);

    @Select("SELECT id, name, `desc`, price, picture FROM goods LIMIT #{limit}")
    List<Goods> selectRelevantGoods(Integer limit);

    /**
     * 通过商品名称查询商品详情
     * @param name
     * @return
     */
    @Select("SELECT * FROM goods WHERE name like #{name}")
    List<GoodsDetail> selectGoodsByName(String name);
}